package com.test.dao;

import com.test.entity.StuAllInfoDto;

public class TeaOperatProvider {

    public String selectAllStu(StuAllInfoDto stuAllInfoDto,String identity){
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT\n" +
                "\ttssi.stu_class,\n" +
                "\ttssi.stu_code,\n" +
                "\ttssi.stu_department,\n" +
                "\ttssi.stu_education,\n" +
                "\ttssi.stu_inTime,\n" +
                "\ttssi.stu_inway,\n" +
                "\ttssi.stu_outTime,\n" +
                "\ttssi.stu_profession,\n" +
                "\ttssi.stu_schoolname,\n" +
                "\ttsbi.stu_address,\n" +
                "\ttsbi.stu_city,\n" +
                "\ttsbi.stu_counselor,\n" +
                "\ttsbi.stu_county,\n" +
                "\ttsbi.stu_ec,\n" +
                "\ttsbi.stu_name,\n" +
                "\ttsbi.stu_ectel,\n" +
                "\ttsbi.stu_email,\n" +
                "\ttsbi.stu_fromaddress,\n" +
                "\ttsbi.stu_fromcity,\n" +
                "\ttsbi.stu_fromprovince,\n" +
                "\ttsbi.stu_idcard,\n" +
                "\ttsbi.stu_sex,\n" +
                "\ttsbi.stu_tel,\n" +
                "tu.u_status,\n" +
                "tu.u_acount,\n" +
                "\ttsji.stu_compaddress,\n" +
                "\ttsbi.stu_qq,\n" +
                "\ttsji.stu_compagreement,\n" +
                "\ttsji.stu_compcity,\n" +
                "\ttsji.stu_compcode,\n" +
                "\ttsji.stu_compcounty,\n" +
                "\ttsji.stu_compjob,\n" +
                "\ttsji.stu_compjobguide,\n" +
                "\ttsji.stu_compjobstatus,\n" +
                "\ttsji.stu_compkind,\n" +
                "\ttsji.stu_compname,\n" +
                "\ttsji.stu_compnature,\n" +
                "\ttsji.stu_updateagreement\n" +
                "FROM\n" +
                "\ttable_user tu\n" +
                "LEFT JOIN table_stubase_info tsbi ON tu.u_acount = tsbi.u_acount\n" +
                "LEFT JOIN table_stustatus_info tssi on tu.u_acount = tssi.u_acount\n" +
                "LEFT JOIN table_stujob_info tsji on tu.u_acount = tsji.u_acount");
        sql.append(" where tssi.stu_code is not null and  tsbi.stu_name is not null");

        if (stuAllInfoDto.getStu_profession()!=null&&!"".equals(stuAllInfoDto.getStu_profession())){
            sql.append(" and  tssi.stu_profession like '%"+stuAllInfoDto.getStu_profession()+"%'");
        }
        if (stuAllInfoDto.getStu_class()!=null&&!"".equals(stuAllInfoDto.getStu_class())){
            sql.append(" and  tssi.stu_class like '%"+stuAllInfoDto.getStu_class()+"%'");
        }
        if (stuAllInfoDto.getStu_code()!=null&&!"".equals(stuAllInfoDto.getStu_code())){
            sql.append(" and  tssi.stu_code like '%"+stuAllInfoDto.getStu_code()+"%'");
        }
        if (stuAllInfoDto.getStu_name()!=null&&!"".equals(stuAllInfoDto.getStu_name())){
            sql.append(" and  tsbi.stu_name like '%"+stuAllInfoDto.getStu_name()+"%'");
        }
        if(identity.equals("未就业")){
            sql.append(" and  tsji.stu_compjobstatus not like '就业%' and tsji.stu_compjobstatus not like '考研%'");
        }else  if(identity.equals("已就业")){
            sql.append(" and  tsji.stu_compjobstatus like '就业%'");
        }else if(identity.equals("升学")){
            sql.append(" and  tsji.stu_compjobstatus like '考研%'");
        }
        return sql.toString();
    }

    public String selectCompCount(){
        StringBuilder sql = new StringBuilder();
        sql.append("select count(u_acount) as num from table_user where u_Identity = '4'");
        return sql.toString();
    }

    public String selectJobCount(){
        StringBuilder sql = new StringBuilder();
        sql.append("select count(u_acount)*7+6*3 as num from table_releasejob_info ");
        return sql.toString();
    }

}


